MySQL 命令语句(二)

前言：

使用命令行启动、登录 MySQL 数据库；

数据库、数据表和数据(记录)的增删查改；

数据(记录)的高级查询；

数据库的备份与恢复。

一、基本命令行

1. MySQL 服务器操作

启动: sudo service mysql start
查看: ps -aux | grep 'mysql'
停止: sudo service mysql stop
重启：sudo service mysql restart

2. MySQL 客户端操作

登录: mysql -u root -p
退出: exit/quit

3. 数据库操作

数据库操作：使用、查看、创建、删除
    
    -- 使用数据库
    use db_xxx;
    
    -- 查看所有数据库
    show databases;
    
    -- 查看当前数据库
    select database();
    
    -- 创建
    create database db_xxx charset=utf8;
    
    -- 查看创建语句
    show create database db_xxx;
    
    -- 删除
    drop database db_xxx;

4. 数据表操作

数据表操作：查看、创建、修改、删除
    
    -- 查看
    show tables;
    
    -- 创建
    create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
    
    -- 修改
    alter table 表名 add|modify|change|drop 列名 类型及约束;
        -- 添加字段 
        alter table 表名 add 列名 类型;
        -- 修改字段：不重命名版
        alter table 表名 modify 列名 类型及约束;
        -- 修改字段：重命名版
        alter table 表名 change 原名 新名 类型及约束;
    
    -- 删除
    drop table 表名;

5. 数据(记录)的增删改查curd

对数据表中数据的操作：增加、删除、修改和基本查询
    
    -- 新增
    insert into 表名(列1,...) values(值1,...)
    
    -- 删除
    delete from 表名 where 条件;
    
    -- 修改
    update 表名 set 列1=值1,列2=值2... where 条件;
    
    -- 基本查询
    select * from 表名;

二、高级查询

1.基本查询

1. 查询所有字段、指定字段的数据

    select * from students;
    select name,age from students;


2. 消除重复行命令distinct

    select distinct gender from students;


3. as给字段、表起别名

    select s.name as '姓名',s.age as '年龄' from students as s;

2.条件查询

1. where后跟比较运算符、逻辑运算符

    比较运算符：>   <   >=  <=  =   !=
    逻辑运算符：and or not 

    -- 优先级：not>and>or，如果同时出现并希望先算or，需要结合()使用


2. 模糊查询like和范围查询in

    name like '%杰'
        % ： 代表任意个字符
        _ : 代表一个字符
    
    id  in (1,3,8)


3. 空判断is null和非空判断is not null

    select * from students where height is null;
    select * from students where  height is not null;

3. 排序

放在 from、where、group by之后

    order by 列1 asc ，列2 desc；
    desc: 降序
    asc：升序，默认

4. 聚合函数

count(*)\count(列)：总数 
max(列): 最大值
min(列): 最小值
sum(列)：求和
avg(列)：求平均

round(avg(price),2) : 四舍五入, 保留两位小数

5. 分组查询

group by + group_concat(字段名)/聚合函数/having

更精细化统计
    group by： 分组
    group_concat： 拼接字符串，拼接字段名
    聚合函数： 最大，最小，平均，计数，求和
    having： 对分组后的结果集进一步筛选
	
注意1: 
    select 列中只能存放分组函数(比如聚合函数)，或是出现在group by子句中的分组标签
    
注意2：
    where: 对源数据做条件筛选，不能接聚合函数
    having: 是对分组之后的数据做进一步的筛选操作, 有having就一定有group by, 有 group by 不一定有having，接聚合函数

6. 分页查询

limit [start],count 

    1. 放在查询语句的最后
    2. start=(page-1)*count

7. 连接查询

inner join on : 内连接(结果仅包含符合连接条件的两表中的行)

left join on : 左连接(完全显示左表所有的行，如果左表中某行 在右表中没有匹配的行，则右表该行显示NULL)

right join on : 右连接(与左连接相反)

8. 子查询

查询中嵌套查询：
分为三种：
    1.标量子查询: 子查询的结果为一个值(一行一列)
    2.列子查询：  子查询的结果为一个列(一列多行)
    3.表子查询:   子查询的结果为一个表(多行多列)
    
例子：
    1. 查出高于平均身高的信息
        1. (select avg(height) from students) // 结果：172
	    2. select * from students where height>(select avg(height) from students);
	    
	2. 查出能够对应上班级号的学生信息
	    1. (select id from classes)  // 结果：list(1,2)
	    2. select * from students where cls_id in (select id from classes);
	  
	3. 查询编号小于6的男性同学的姓名
	    1. (select * from students where id<6) // 结果：一张表
	    2. select name from (select * from students where id<6) as s  where gender='男';

9. 自关联查询

通俗讲，就是自己关联自己,再通过内连接实现数据查询

1. 向areas插入记录
    source areas.sql;
	提示：areas.sql 文件要存放在 mysql 登录的目录中
	
2. 查询广东省中的所有城市
    第一种方式(两条sql语句)：
        1. 查广东省的id
        select aid  from areas where atitle='广东省'; // 440000 

        2. 再把省的id作为pid，查出对应的城市信息
        select * from areas where pid=440000;

    第二种方式(子查询)：
        select * from areas where pid=(select aid from areas where atitle='广东省');

    第三种方式(自关联)： 
        select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='广东省';

三、查询总结

查询的完整格式（^_^ 不要被吓到其实很简单 ! _ !）

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整的select语句

select distinct *
    from tb_name
    where ....
    group by ... having ...
    order by ...
    limit start,count

执行顺序

from tb_name
    where ....
    group by ...
    select distinct *
    having ...
    order by ...
    limit start,count

实际使用中，只是语句中某些部分的组合，而不是全部

四、备份/还原

1. 备份

1. 冷备份： 数据库停止再备份

2. 热备份： 很多情况数据库运行时备份

    逻辑备份，备份的是数据库相关的sql语句
    适合中小型数据的备份

    mysqldump –uroot –p 数据库名 > python.sql;

2. 还原

1. 创建新数据库

    create database 新数据库名 charset=utf8;

2. 从sql文件中导入数据

    mysql -uroot –p 新数据库名 < python.sql